<?php
/*
 * ebiz front
 *
 * Metadata Model
 *
 * @author allen
 *
 */

require_once '../app/core/models/BaseDao.php';
require_once '../app/core/db/DBUtil.class.php';

class MetadataDao extends BaseDao
{
    private $tableName ="metadatas";

 	public function getMetadataById($id){
 		$param = array($id);
    	$sql="select * from $this->tableName where id=? and is_delete = 0";
    	return $this->dbUtil->SelectData($sql, $param);
    }

// 	public function getGroupByItemId($itemId){
// 		$param = array($itemId);
//    	$sql="select    distinct (  m.group_id  )   ,   mg.title as mgtitle   from  item_metadatas im
//			  join metadatas m on im.metadata_id = m.id
//			  join metadata_groups mg   on  m.group_id = mg.id
//			  where  product_item_id = ? and im.is_delete = 0
//			  order by mg.sort_order";
//    	return $this->dbUtil->SelectData($sql,$param);
//    }
//
// 	public function getMetadataByItemId($itemId){
// 		$param = array($itemId);
//    	$sql="select   im.product_item_id as itemid  ,  im.metadata_id  as  metadataid  ,  m.title  ,  m.default_value  as  mvalue  ,  mg.title  as  mgtitle
//			  from  item_metadatas im
//			  join metadatas m on im.metadata_id = m.id
//			  join metadata_groups mg   on  m.group_id = mg.id
//			  where  product_item_id = ?
//			  order by mg.sort_order  ,   m.sort_order";
//    	return $this->dbUtil->SelectData($sql,$param);
//    }
 	public function getGroupByItemId($itemId){
 		$param = array($itemId);
    	$sql="select   *
			from metadata_groups mg
			join products p on p.metadata_set_id = mg.metadata_set_id
			join product_items pis on pis.product_id = p.id
			where pis.id=1 and pis.is_delete=0";
    	return $this->dbUtil->SelectData($sql,$param);
    }

 	public function getMetadataByItemId($itemId){
 		$param = array($itemId);
    	$sql="select   im.product_item_id as itemid  ,  im.metadata_id  as  metadataid  ,  m.title  ,  m.default_value  as  mvalue  ,  mg.title  as  mgtitle
			  from  item_metadatas im
			  join metadatas m on im.metadata_id = m.id
			  join metadata_groups mg   on  m.group_id = mg.id
			  where  product_item_id = ?
			  order by mg.sort_order  ,   m.sort_order";
    	return $this->dbUtil->SelectData($sql,$param);
    }

    public function getMetadataBySet($setId){
        $param = array($setId);
        $sql="select `metadatas`.*,
        `metadata_groups`.`metadata_set_id`, `metadata_groups`.`title` as mgtitle ,
        `metadata_sets`.`id` as `set_id` , `metadata_sets`.`title` as mstitle
        from `metadatas`
        join `metadata_groups` on metadata_groups.id = metadatas.group_id
        join `metadata_sets` on metadata_sets.id = metadata_groups.metadata_set_id
        where metadatas.is_delete = 0 and metadata_sets.id = ? and in_basic_search = 1
        order by `metadatas`.`sort_order`";
        return $this->dbUtil->SelectData($sql,$param);
    }

    /**
     * 根据categories.id返回全部的metadata_options
     *
     * @param int $int_categoryid
     * @return array
     */
    public function getMetadatasByCategoryId($int_categoryid) {
    	$str_sql = "
    	   SELECT
    	       `c`.`id` AS `category_id`,
    	       `m`.`id` AS `metadata_id`,
    	       `m`.`sort_order` AS `metadata_index`,
    	       `m`.`title` AS `metadata_title`,
    	       `mo`.`id` AS `option_id`,
        	   `mo`.`sort_order` AS `option_index`,
    	       `mo`.`title` AS `option_title`,
    	       `mo`.`value` AS `option_value`
    	   FROM `metadata_options` AS `mo`
    	       LEFT JOIN `metadatas` AS `m` ON `mo`.`metadata_id` = `m`.`id`
    	       LEFT JOIN `metadata_groups` AS `mg` ON `m`.`group_id` = `mg`.`id`
    	       LEFT JOIN `metadata_sets` AS `ms` ON `mg`.`metadata_set_id` = `ms`.`id`
    	       LEFT JOIN `categories` AS `c` ON `c`.`metadata_set_id` = `ms`.`id`
    	   WHERE `c`.`id` = ?
    	       AND `m`.`input_type` = '3'
    	       AND `m`.`in_basic_search` = '1'
    	       AND `mo`.`is_delete` = '0'
    	       AND `m`.`is_delete` = '0'
    	   GROUP BY `mo`.`id`
    	   ORDER BY `m`.`sort_order`, `mo`.`sort_order`, `mo`.`created` DESC
    	";
    	return $this->dbUtil->SelectData($str_sql, array($int_categoryid));
    }

    public function getMetadatasetByCategoryId($int_id){
        $str_sql = "
            SELECT `ms`.* FROM `metadata_sets` AS `ms`
                LEFT JOIN `categories` AS `c` ON `c`.`metadata_set_id` = `ms`.`id`
            WHERE `c`.`id` = ?
        ";
        return $this->dbUtil->SelectData($str_sql, array($int_id));
    }

    public function getMetadataIdsByMetadatasetId($int_id){
        $str_sql = "
            SELECT `m`.`id` FROM `metadatas` AS `m`
                LEFT JOIN `metadata_groups` AS `mg` ON `m`.`group_id` = `mg`.`id`
            WHERE `mg`.`metadata_set_id` = ? AND `m`.`is_delete` = 0
        ";
        return $this->dbUtil->SelectData($str_sql, array($int_id));
    }

}